Expression Editor

The Expression Editor is used to create formulas and other calculated expressions. It is available for many types of fields; when it is available for a field, the Expressions button on the ribbon will become active.

expression editor

You can use a variety of data as function arguments. Available arguments are listed on tabs depending on their type. The Periodic tab lists all variables which contain multiple values. The Scalar tab lists variables that have a single value. The System tab lists other items such as current case and scenario names, periods, and ID values.

Use the Validate button to test whether the expression is accepted as valid.

Clicking the Clear Expression button will delete the edited expression completely.

The list of available functions includes standard mathematical and statistical functions, as well as custom functions specific to Dataflow. When you select a function from the list, its description is displayed in the Function Description box, including the required arguments.

Function descriptions

Note: The basic mathematical functions (ABS, ATAN, AVERAGE, CEILING, COS, COSH, EXP, FLOOR, INTPOWER, LN, LOG, LOG10, MAX, MIN, MOD, POWER, RAND, ROUND, SIGN, SIN, SINH, SQRT, SUM, TAN, TANH), text-manipulation functions (CONCATENATE, FLOAT, INSTR, LEN, LOWER, LTRIM, MID, RTRIM, STRLIKE, TEXT, TRIM, UPPER), and logic functions (IF, ISNULL) are not included in the following table.

Function	Description
CONVERTTOREAL(variable)	Returns the values of the periodic numeric variable in real terms. variable: a periodic numeric variable. Change from version 16.5: In version 20, the optional input parameter currency has been removed; the version 20 calculation engine converts all variable currencies to the template currency before any calculation, and calculated currency values are saved in the template currency.
CUMULATIVE(periodic_variable)	Gets the cumulative periodic values of the given periodic variable. Example: If Var1=1,2,3,4,5, CUMULATIVE([Var1]) = 1,3,6,10,15
CUMULATIVERANGE(variable, start, end)	Calculates the cumulative range value of a periodic variable: variable: periodic variable start: range start period index or date end: range end period index or date Example: If Var1=1,2,3,4,5 over a period 2010 to 2014. Range specified by integers: CUMULATIVERANGE([Var1],1,3) = null,2,5,9,null Range specified by date: CUMULATIVERANGE([Var1],"1/1/2011","12/1/2013") = null,2,5,9,null
DATE(year,month,day)	Generates a date representation from the input year, month and day. This type of date is independent of the operating system regional setting and date format.
DATEADD(datepart, number, date)	Returns the result date when a specified integer number of intervals is added to one part of the input date. datepart: the part of the date to which an integer number will be added. The allowed values are y, m, d, for year, month, day, respectively number: an expression that can be resolved to an integer which will be added to datepart date: an expression that can be resolved to a date. This can either be a date variable, a string variable that can be converted to a date, or text that can be converted to a date Example: DATEADD("y", 1, "1 January 2010") gives the result "1 January 2011". (Note: This function will not evaluate if the resultant date is less than 1 January 0001 or greater than 31 December 9999.)
DATECOMPARE(date1, date2)	Compares the two input dates and returns: -1 if date1 > date2 0 if date1 = date2 1 if date1 < date2 date1 and date2 can be date variables or text values. Text values will be converted to dates using the operating system regional settings. Example: DATECOMPARE("1 January 2000", "1 January 2010") returns 1. DATECOMPARE("1 January 2010", "1 January 2000") returns -1
DATEPART(date, datepart)	Extracts the specified part of an input date and returns a number. date: a date variable or a text value. A text value will be converted to a date using the operating system regional settings datepart: y for Year, m for Month, or d for Day. Example: DATEPART("31/12/2006", "y") returns the value 2006.
DATETEXT(date, [format])	Converts the input date to text. If the optional format argument is supplied, the date will be formatted using that format, otherwise the operating system regional settings will be followed. Examples: DATETEXT([DateVariable1]), DATETEXT([DateVariable1],""MM/dd/yyyy""). Format specifiers: d: day 1 - 31 dd: day 01 - 31 M: month 1 - 12 MM: month 01 - 12 MMM: abbreviated month name (e.g. Jan, Feb) MMMM: full month name yy: year 00 - 99 yyyy: 4-digit year
DATETONUMERIC(date)	Converts the input date to a numeric value using the 'OLE Automation Date' format (i.e., the number of days after or before midnight on 30 December 1899). Example: DATEONNUMERIC("1 January 1900") gets a numeric value 2.
DEFERREDRATE(production, deferment, [use_linear_values])	Outputs a periodic variable of deferred production rates for future production, based on the input production variable and deferment schedule (note: the cumulative volume planned is used to identify the production rate, after factoring in deferred days). production: periodic variable for production rate deferment: periodic variable which represents the deferment percentages use_linear_values: optional setting with allowed values "True" or "False" (note: quote characters must be included in the function call, "False" is the default setting)
FIRSTNONZERODATE(periodic_variable)	Finds the first non-zero value of the input periodic variable and returns the date of the period in which it occurs. Returns null if no non-zero values are found.
FIRSTNONZEROPERIODINDEX(periodic_variable)	Finds the first non-zero value of the input periodic variable and returns the zero-based index of the period in which it occurs. Returns -1 if a non-zero value is not found.
GETHIERARCHYPART([Hierarchy Location], part_id)	Returns the hierarchy part parsed from the input hierarchy location, according to the input part identifier. part_id can be either: the hierarchy level (0 = root node, 1 = first level, etc.), a level name (e.g., "Country", "Region"), or a special identifier ("_last" = last node, "_parent" = parent of last node). Examples: if the input hierarchy node is 'Atlantis/Atlantis UK/North Sea': GETHIERARCHYPART([Hierarchy Location],2) returns the name of the node at level 2 in the hierarchy path, i.e. 'North Sea' GETHIERARCHYPART([Hierarchy Location],"Company") returns the name of the Company level node in the hierarchy path, 'Atlantis UK' GETHIERARCHYPART([Hierarchy Location],"_parent") returns the parent node of the last node, 'Atlantis UK'
GETHIERARCHYPATH([Hierarchy Location])	Using the system variable [Hierarchy Location] as input, this function returns the path portion of the location. Example: Given a node that is 3 levels down, GETTHEHIERARCHYPATH ([Hierarchy Location]) will return a text representation of the names of all the nodes to traverse to get to the current node, e.g. "Root Node\Parent Node\Current Node".
GETLISTPROPERTY(listvariable, property, [default])	Returns a property value from a list which is assigned to a variable within a template. listvariable: scalar variable for the assigned list. property: the list property for the required return value. default: optional input to specify a text string which will be used as the function return value if a match is not found for the specified list and property. Example: A list is created to store a set of country names with extra properties for notes (note) and country codes (code). The list is then assigned to the variable CountryInfo within a template. 1. GETLISTPROPERTY([CountryInfo],"note") gets the value of note for the currently-selected country 2. GETLISTPROPERTY([CountryInfo],"code") gets the value of code for the currently-selected country 3. GETLISTPROPERTY([CountryInfo], "code", "GB") returns the value 'GB' if a match is not found for the given list and property.
GETPRICESCENARIODATA(price, scenario)	Returns the periodic data for a product stream's linked price, for the input scenario. price: the price linked to the product (to be selected from the list of prices displayed in the Price tab under Function Arguments). scenario: a price deck scenario name. Example: GETPRICESCENARIODATA([Oil 1 Price],"High")
GETSCENARIODATA(scenario, variable, [default])	Returns data for the specified variable and scenario. scenario: a scenario name. variable: a scalar or periodic variable (supported types: TimeSeriesDouble, ScalarDouble, ScalarInteger, ScalarString, ScalarBoolean, ScalarDateTime, Working Interest, Array Double). default: optional input to specify a value which will be used as the function return value if the specified scenario does not exist in the document. Examples: GETSCENARIODATA([1C],[Production.Oil]): gets the value of variable 'Production.Oil' for scenario '1C' GETSCENARIODATA([1C],[Production.Oil],100): returns the default value 100 if scenario '1C' does not exist
LASTNONZERODATE(periodic_variable)	Returns the last non-zero value contained in the input periodic variable, and the corresponding date. The function returns null if no non-zero values are found.
MAXPERIODIC(periodic_variable)	Returns the maximum value contained in the input periodic variable.
NPV(rate, variable, [discountdate], [discountmethod], [donotdiscounthistoricalperiods])	Returns the net present value of an investment based on a discount rate and a series of values. Discount date and calculation method can be optionally specified. rate: a scalar value for the discount rate to be used in calculations; can be either a numerical value or a scalar variable. variable: a periodic numeric variable, usually representing a cash flow. discountdate: (optional) datetime variable or a date string to specify when NPV will be applied from; if not specified then the document start date is used. discountmethod: (optional) the discount method; the default method is 'Monthly', other available methods are: 'Annual Start', 'Annual Mid', 'Annual End'. donotdiscounthistoricalperiods (optional): boolean value to specify whether historical periods are discounted or not; default value is 'false'. Examples: 1. NPV([rate1], [cashflow1]): calculate NPV for the data in variable cashflow1 using the discount rate in variable rate1. 2. NPV("10", [cashflow2], "12/31/2022", "Annual Start", "true"): calculate NPV for cashflow2 using the discount rate 10%, discount method 'Annual Start', and historical periods will not be discounted.
NUMERICTODATE(numeric_date)	Converts the input numeric date, based on the 'OLE Automation date' format (i.e., the number of days after or before midnight on 30 December 1899), to a date string. The input value must be in the range -657435 to 2958466. Example: NUMERICTODATE(2) returns the date '1 January 1900'.
PARSEDATE(text, [format])	Converts the input text string to a date object suitable for use in date editors or functions requiring a date argument. If the optional format argument is input, the text will be parsed using this specification, otherwise the operating system regional settings will be applied. Note that the regional settings or a supplied format are always used when parsing the input text to determine a resultant date. To format a date directly as text, use the DATETEXT function instead. Examples: PARSEDATE("31 January 2010"), PARSEDATE("01/31/2010","MM/dd/yyyy") Format specifiers: d: day 1 - 31 dd: day 01 - 31 M: month 1 - 12 MM: month 01 - 12 MMM: abbreviated month name (e.g. Jan, Feb) MMMM: full month name yy: year 00 - 99 yyyy: four-digit year
PERIODICAVERAGE(variable, [periodicity])	Returns the average (arithmetic mean) of the input periodic variable over a specified periodicity. Only periods with data (including 0 values) will be evaluated. variable: a periodic variable. periodicity: optional parameter to specify the periodicity ('Annually', 'Semi-annually', 'Quarterly', 'Monthly'); if this parameter is not supplied then the document template periodicity will be used; if this is Mixed, then Annually will be used.
RANGE(variable, start, end)	Calculates the total value of a periodic variable for the given range. variable: periodic variable. start: range start year. end: range end year. Example: If Var1 = 1,2,3,4,5 over a period 2010 to 2014. 1. Using integer range parameters: RANGE([Var1],1,3) = 9,9,9,9,9 2. Using range parameters RANGE([Var1],"1/1/2011","12/1/2013") = 9,9,9,9,9 Note: if the function output is assigned to a scalar variable then the output is converted to a scalar value which is the sum of the calculated range values.
ROR(periodic_variable, [discountmethod])	Returns the ROR of the input periodic variable. discountmethod: optional parameter to specify the discount method ('Monthly', 'Annual Start', 'Annual Mid', 'Annual End').
TOTAL(periodic_variable)	Returns the total (sum) of all of the values contained in the input periodic variable.
VALUEBYOFFSET(variable, offset, [default])	Returns the value of the input variable for the period specified by offset. For example, an offset of -1 will return the previous period's value. default is an optional default value that will be returned when no value exists for the given offset.
VALUEBYPERIODINDEX(variable, index, [default])	Returns the value of the input variable for the period specified by the zero-based index (i.e., index 0 is the first period value). default is an optional default value that will be returned when no value exists for the given index.
VALUEBYSUBCATEGORY(variable, subcategory, [default])	Returns the value of the input variable for the index specified by subcategory. default is an optional default value that will be returned when no value is found for the given subcategory.